package demo.database.sql2o.Sql2o;

import java.sql.Connection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.sql2o.Sql2o;

import com.titan.core.utils.db.DBUtils;

import demo.database.hsqldb.MemoryOnlyDBManager;


/**
 *
 *
 * @author Administrator
 *
 */
public class FetchingData {
    public static void main(String[] args) {
        try {
            Connection conn = null;
            try {
                // 初始化数据表
                conn = MemoryOnlyDBManager.getConnection();
                DBUtils.execute(conn, "CREATE TABLE tasks(id IDENTITY, description VARCHAR(100), duedate TIMESTAMP)");
                for (int i = 0; i < 5; i++) {
                    DBUtils.execute(conn, "insert into tasks(id,description,duedate) values(null, 'description"+i+"', TIMESTAMP'2000-12-10 10:11:12')");
                }
            } finally {
                DBUtils.close(conn);
            }

            // 查询结果返回列表数据
            getAllTasks();
            // 执行带有参数的查询
            getTasksBetweenIds();
            // 执行带有列映射的查询
            getTasksBetweenIds2();
            // 执行带有批量列映射的查询
            getTasksBetweenIds3();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    
    // 执行带有列映射的查询
    private static void getTasksBetweenIds2() throws Exception {
        String sql =
            "SELECT id, description, duedate " +
            "FROM tasks " +
            "WHERE id >= :fromId AND id < :toId";

        List<TaskDomain> items = Configuration.getSql2o()
                .createQuery(sql)
                .addParameter("fromId", "2")
                .addParameter("toId", "4")
                .addColumnMapping("id", "id2")
                .addColumnMapping("description", "description2")
                .addColumnMapping("duedate", "duedate2")
                .executeAndFetch(TaskDomain.class);
        for (TaskDomain task : items) {
            System.out.println("getTasksBetweenIds2方法： "+task);
        }
    }
    // 执行带有批量列映射的查询
    private static void getTasksBetweenIds3() throws Exception {
        String sql =
            "SELECT id, description, duedate " +
            "FROM tasks " +
            "WHERE id >= :fromId AND id < :toId";
        
        Map<String, String> colMaps = new HashMap<String,String>();
        colMaps.put("id", "id2");
        colMaps.put("description", "description2");
        colMaps.put("duedate", "duedate2");
        
        Sql2o sql2o = Configuration.getSql2o();
        sql2o.setDefaultColumnMappings(colMaps);
        
        List<TaskDomain> items = sql2o.createQuery(sql)
                .addParameter("fromId", "2")
                .addParameter("toId", "4")
                .executeAndFetch(TaskDomain.class);
        for (TaskDomain task : items) {
            System.out.println("getTasksBetweenIds3方法： "+task);
        }
    }
    // 执行带有参数的查询
    private static void getTasksBetweenIds() throws Exception {
        String sql =
            "SELECT id, description, duedate " +
            "  FROM tasks " +
            " WHERE id >= :fromId AND id < :toId";

        List<TaskDomain> items = Configuration.getSql2o().createQuery(sql).addParameter("fromId", "2").addParameter("toId", "4").executeAndFetch(TaskDomain.class);
        for (TaskDomain task : items) {
            System.out.println("getTasksBetweenIds方法： "+task);
        }
    }
    
    // 查询结果返回列表数据
    private static void getAllTasks() throws Exception {
        String sql = "SELECT id, description, duedate FROM tasks";
        
        List<TaskDomain> items = Configuration.getSql2o().createQuery(sql).executeAndFetch(TaskDomain.class);
        for (TaskDomain task : items) {
            System.out.println("getAllTasks方法： "+task);
        }
        
        // 如下代码段不能被执行
        /*
        List<Map<String,Object>> items2 = Configuration.getSql2o().createQuery(sql).executeAndFetch(Map.class);
        for (Map<String,Object> task : items2) {
            System.out.println(task);
        }
        */
    }    
}

